package cn.iocoder.yudao.module.system.dal.mysql.ai;

import cn.iocoder.yudao.framework.common.pojo.PageResult;
import cn.iocoder.yudao.framework.mybatis.core.mapper.BaseMapperX;
import cn.iocoder.yudao.framework.mybatis.core.query.LambdaQueryWrapperX;
import cn.iocoder.yudao.module.system.controller.admin.ai.vo.AiAdmissionCutoffScorePageReqVO;
import cn.iocoder.yudao.module.system.dal.dataobject.ai.AiAdmissionCutoffScoreDO;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

/**
 * AI 录取分数线 Mapper
 *
 * @author 芋道源码
 */
@Mapper
public interface AiAdmissionCutoffScoreMapper extends BaseMapperX<AiAdmissionCutoffScoreDO> {

    /**
     * 根据院校名称查询录取分数线数据
     *
     * @param schoolName 院校名称
     * @param province 省份
     * @param batchName 批次名称
     * @return 录取分数线数据列表
     */
    default List<AiAdmissionCutoffScoreDO> selectBySchoolName(String schoolName, String province, String batchName) {
        return selectList(new LambdaQueryWrapperX<AiAdmissionCutoffScoreDO>()
                .likeIfPresent(AiAdmissionCutoffScoreDO::getSchoolName, schoolName)
                .eqIfPresent(AiAdmissionCutoffScoreDO::getProvince, province)
                .eqIfPresent(AiAdmissionCutoffScoreDO::getBatchName, batchName)
                .orderByAsc(AiAdmissionCutoffScoreDO::getSchoolName)
                .orderByAsc(AiAdmissionCutoffScoreDO::getGroupName));
    }

    /**
     * 根据科目类型查询录取分数线数据
     *
     * @param type 科目类型
     * @return 录取分数线数据列表
     */
    default List<AiAdmissionCutoffScoreDO> selectByType(String type) {
        return selectList(new LambdaQueryWrapperX<AiAdmissionCutoffScoreDO>()
                .eq(AiAdmissionCutoffScoreDO::getType, type)
                .orderByAsc(AiAdmissionCutoffScoreDO::getSchoolName));
    }

    /**
     * 根据专业组查询录取分数线数据
     *
     * @param groupName 专业组
     * @return 录取分数线数据列表
     */
    default List<AiAdmissionCutoffScoreDO> selectByGroupName(String groupName) {
        return selectList(new LambdaQueryWrapperX<AiAdmissionCutoffScoreDO>()
                .likeIfPresent(AiAdmissionCutoffScoreDO::getGroupName, groupName)
                .orderByAsc(AiAdmissionCutoffScoreDO::getSchoolName));
    }

    /**
     * 根据选科要求查询录取分数线数据
     *
     * @param subjectRequirement 选科要求
     * @return 录取分数线数据列表
     */
    default List<AiAdmissionCutoffScoreDO> selectBySubjectRequirement(String subjectRequirement) {
        return selectList(new LambdaQueryWrapperX<AiAdmissionCutoffScoreDO>()
                .likeIfPresent(AiAdmissionCutoffScoreDO::getSubjectRequirement, subjectRequirement)
                .orderByAsc(AiAdmissionCutoffScoreDO::getSchoolName));
    }

    /**
     * 分页查询录取分数线数据
     *
     * @param reqVO 查询条件
     * @return 分页结果
     */
    default PageResult<AiAdmissionCutoffScoreDO> selectPage(AiAdmissionCutoffScorePageReqVO reqVO) {
        return selectPage(reqVO, new LambdaQueryWrapperX<AiAdmissionCutoffScoreDO>()
                .eqIfPresent(AiAdmissionCutoffScoreDO::getSchoolName, reqVO.getSchoolName())
                .eqIfPresent(AiAdmissionCutoffScoreDO::getProvince, reqVO.getProvince())
                .eqIfPresent(AiAdmissionCutoffScoreDO::getBatchName, reqVO.getBatchName())
                .eqIfPresent(AiAdmissionCutoffScoreDO::getYear, reqVO.getYear())
                .eqIfPresent(AiAdmissionCutoffScoreDO::getType, reqVO.getType())
                .likeIfPresent(AiAdmissionCutoffScoreDO::getGroupName, reqVO.getGroupName())
                .likeIfPresent(AiAdmissionCutoffScoreDO::getSubjectRequirement, reqVO.getSubjectRequirement())
                .likeIfPresent(AiAdmissionCutoffScoreDO::getRemark, reqVO.getRemark())
                .betweenIfPresent(AiAdmissionCutoffScoreDO::getCreateTime, reqVO.getCreateTime())
                .orderByDesc(AiAdmissionCutoffScoreDO::getId));
    }

    /**
     * 根据院校名称和科目类型查询AI录取分数线
     *
     * @param schoolName 院校名称
     * @param type 科目类型
     * @param province 省份
     * @param batchName 批次名称
     * @return AI录取分数线列表
     */
    default List<AiAdmissionCutoffScoreDO> selectBySchoolNameAndType(String schoolName, String type, String province, String batchName) {
        return selectList(new LambdaQueryWrapperX<AiAdmissionCutoffScoreDO>()
                .eq(AiAdmissionCutoffScoreDO::getSchoolName, schoolName)
                .eq(AiAdmissionCutoffScoreDO::getType, type)
                .eqIfPresent(AiAdmissionCutoffScoreDO::getProvince, province)
                .eqIfPresent(AiAdmissionCutoffScoreDO::getBatchName, batchName));
    }

    /**
     * 根据院校名称获得AI录取分数线列表
     *
     * @param schoolName 院校名称
     * @param province 省份
     * @param batchName 批次名称
     * @return AI录取分数线列表
     */
    default List<AiAdmissionCutoffScoreDO> selectListBySchoolName(String schoolName, String province, String batchName) {
        return selectBySchoolName(schoolName, province, batchName);
    }

    /**
     * 根据院校名称和科目类型获得AI录取分数线列表
     *
     * @param schoolName 院校名称
     * @param type 科目类型
     * @param province 省份
     * @param batchName 批次名称
     * @return AI录取分数线列表
     */
    default List<AiAdmissionCutoffScoreDO> selectListBySchoolNameAndType(String schoolName, String type, String province, String batchName) {
        return selectBySchoolNameAndType(schoolName, type, province, batchName);
    }

    /**
     * 根据分数范围查询可填报的学校名称列表
     *
     * @param minScore 最低分
     * @param maxScore 最高分
     * @param type 科目类型
     * @param year 年份
     * @param province 省份
     * @param batchName 批次名称
     * @return 学校名称列表
     */
    default List<String> selectSchoolNamesByScoreRange(Integer minScore, Integer maxScore, String type, Integer year, String province, String batchName) {
        // 查询所有符合类型条件的记录（不使用groupBy，避免类型转换问题）
        List<AiAdmissionCutoffScoreDO> allResults = selectList(new LambdaQueryWrapperX<AiAdmissionCutoffScoreDO>()
                .eqIfPresent(AiAdmissionCutoffScoreDO::getType, type)
                .eqIfPresent(AiAdmissionCutoffScoreDO::getYear, year)
                .eqIfPresent(AiAdmissionCutoffScoreDO::getProvince, province)
                .eqIfPresent(AiAdmissionCutoffScoreDO::getBatchName, batchName)
                .isNotNull(AiAdmissionCutoffScoreDO::getLowerScore)
                .ne(AiAdmissionCutoffScoreDO::getLowerScore, ""));
        
        // 在内存中过滤分数范围并去重（因为lower_score是字符串类型，需要转换）
        return allResults.stream()
                .filter(record -> {
                    try {
                        String lowerScoreStr = record.getLowerScore();
                        if (lowerScoreStr == null || lowerScoreStr.trim().isEmpty()) {
                            return false;
                        }
                        Integer lowerScore = Integer.parseInt(lowerScoreStr.trim());
                        
                        // 检查分数范围
                        if (minScore != null && lowerScore < minScore) {
                            return false;
                        }
                        if (maxScore != null && lowerScore > maxScore) {
                            return false;
                        }
                        return true;
                    } catch (NumberFormatException e) {
                        // 如果分数不是有效数字，则过滤掉
                        return false;
                    }
                })
                .map(AiAdmissionCutoffScoreDO::getSchoolName)
                .distinct()
                .collect(java.util.stream.Collectors.toList());
    }

    /**
     * 根据年份、学校、选科、专业组查询最低分和最低位次
     * 
     * @param year 年份
     * @param schoolName 学校名称
     * @param type 科目类型
     * @param groupName 专业组名称
     * @param province 省份
     * @param batchName 批次名称
     * @return 录取分数线记录
     */
    default AiAdmissionCutoffScoreDO selectByYearSchoolTypeGroup(Integer year, String schoolName, String type, String groupName, String province, String batchName) {
        return selectOne(new LambdaQueryWrapperX<AiAdmissionCutoffScoreDO>()
                .eqIfPresent(AiAdmissionCutoffScoreDO::getYear, year)
                .eqIfPresent(AiAdmissionCutoffScoreDO::getSchoolName, schoolName)
                .eqIfPresent(AiAdmissionCutoffScoreDO::getType, type)
                .eqIfPresent(AiAdmissionCutoffScoreDO::getGroupName, groupName)
                .eqIfPresent(AiAdmissionCutoffScoreDO::getProvince, province)
                .eqIfPresent(AiAdmissionCutoffScoreDO::getBatchName, batchName)
                .isNotNull(AiAdmissionCutoffScoreDO::getLowerScore)
                .ne(AiAdmissionCutoffScoreDO::getLowerScore, "")
                .ne(AiAdmissionCutoffScoreDO::getLowerScore, "-")
                .orderByAsc(AiAdmissionCutoffScoreDO::getLowerScore)
                .last("LIMIT 1"));
    }

    /**
     * 【性能优化】批量查询录取分数线数据（根据学校名称列表）
     * 用于解决N+1查询问题
     * 
     * @param year 年份
     * @param schoolNames 学校名称列表
     * @param type 科目类型
     * @param province 省份
     * @param batchName 批次名称
     * @return 录取分数线数据列表
     */
    default List<AiAdmissionCutoffScoreDO> selectBatchByYearSchoolsType(Integer year, List<String> schoolNames, String type, String province, String batchName) {
        if (schoolNames == null || schoolNames.isEmpty()) {
            return new java.util.ArrayList<>();
        }
        
        return selectList(new LambdaQueryWrapperX<AiAdmissionCutoffScoreDO>()
                .eqIfPresent(AiAdmissionCutoffScoreDO::getYear, year)
                .in(AiAdmissionCutoffScoreDO::getSchoolName, schoolNames)
                .eqIfPresent(AiAdmissionCutoffScoreDO::getType, type)
                .eqIfPresent(AiAdmissionCutoffScoreDO::getProvince, province)
                .eqIfPresent(AiAdmissionCutoffScoreDO::getBatchName, batchName)
                .isNotNull(AiAdmissionCutoffScoreDO::getLowerScore)
                .ne(AiAdmissionCutoffScoreDO::getLowerScore, "")
                .ne(AiAdmissionCutoffScoreDO::getLowerScore, "-"));
    }
    
    /**
     * 【性能优化】批量查询录取分数线数据（根据学校UUID列表）
     * 使用school_uuid查询，相比学校名称更精确高效
     * 
     * @param year 年份
     * @param schoolUuids 学校UUID列表
     * @param type 科目类型
     * @param province 省份
     * @param batchName 批次名称
     * @return 录取分数线数据列表
     */
    default List<AiAdmissionCutoffScoreDO> selectBatchByYearSchoolUuidsType(Integer year, List<String> schoolUuids, String type, String province, String batchName) {
        if (schoolUuids == null || schoolUuids.isEmpty()) {
            return new java.util.ArrayList<>();
        }
        
        return selectList(new LambdaQueryWrapperX<AiAdmissionCutoffScoreDO>()
                .eqIfPresent(AiAdmissionCutoffScoreDO::getYear, year)
                .in(AiAdmissionCutoffScoreDO::getSchoolUuid, schoolUuids)
                .eqIfPresent(AiAdmissionCutoffScoreDO::getType, type)
                .eqIfPresent(AiAdmissionCutoffScoreDO::getProvince, province)
                .eqIfPresent(AiAdmissionCutoffScoreDO::getBatchName, batchName)
                .isNotNull(AiAdmissionCutoffScoreDO::getLowerScore)
                .ne(AiAdmissionCutoffScoreDO::getLowerScore, "")
                .ne(AiAdmissionCutoffScoreDO::getLowerScore, "-"));
    }

    /**
     * 【提前分页优化】直接从AI录取分数线表获取排序好的专业组列表（带分页）
     * 用于优化recommendVolunteers接口性能，减少不必要的数据查询
     * 
     * @param province 省份
     * @param year 年份
     * @param type 科目类型（物理类/历史类）
     * @param batchName 批次名称
     * @param minScore 最低分数（可选）
     * @param maxScore 最高分数（可选）
     * @param offset 偏移量 (pageNum-1) * pageSize
     * @param limit 查询条数（建议为 pageSize * 1.5 以应对后续筛选）
     * @return 专业组列表（已按分数降序排序、已分页）
     */
    default List<AiAdmissionCutoffScoreDO> selectMajorGroupsWithPagination(
            String province, Integer year, String type, String batchName,
            Integer minScore, Integer maxScore, Integer offset, Integer limit) {
        
        LambdaQueryWrapper<AiAdmissionCutoffScoreDO> wrapper = new LambdaQueryWrapperX<AiAdmissionCutoffScoreDO>()
                .eqIfPresent(AiAdmissionCutoffScoreDO::getProvince, province)
                .eqIfPresent(AiAdmissionCutoffScoreDO::getYear, year)
                .eqIfPresent(AiAdmissionCutoffScoreDO::getType, type)
                .eqIfPresent(AiAdmissionCutoffScoreDO::getBatchName, batchName)
                .isNotNull(AiAdmissionCutoffScoreDO::getLowerScore)
                .ne(AiAdmissionCutoffScoreDO::getLowerScore, "")
                .ne(AiAdmissionCutoffScoreDO::getLowerScore, "-");
        
        // 分数区间筛选（在内存中过滤，因为lower_score是字符串类型）
        List<AiAdmissionCutoffScoreDO> allResults = selectList(wrapper);
        
        // 过滤分数范围并转换为整数用于排序
        java.util.List<AiAdmissionCutoffScoreDO> filteredResults = allResults.stream()
                .filter(record -> {
                    try {
                        String lowerScoreStr = record.getLowerScore();
                        if (lowerScoreStr == null || lowerScoreStr.trim().isEmpty()) {
                            return false;
                        }
                        Integer lowerScore = Integer.parseInt(lowerScoreStr.trim());
                        
                        // 检查分数范围
                        if (minScore != null && lowerScore < minScore) {
                            return false;
                        }
                        if (maxScore != null && lowerScore > maxScore) {
                            return false;
                        }
                        return true;
                    } catch (NumberFormatException e) {
                        return false;
                    }
                })
                .sorted((a, b) -> {
                    // 按分数降序排序
                    try {
                        Integer scoreA = Integer.parseInt(a.getLowerScore().trim());
                        Integer scoreB = Integer.parseInt(b.getLowerScore().trim());
                        return scoreB.compareTo(scoreA); // 降序
                    } catch (NumberFormatException e) {
                        return 0;
                    }
                })
                .skip(offset)
                .limit(limit)
                .collect(java.util.stream.Collectors.toList());
        
        return filteredResults;
    }

    /**
     * 【提前分页优化】统计符合条件的专业组总数
     * 用于计算分页信息
     * 
     * @param province 省份
     * @param year 年份
     * @param type 科目类型
     * @param batchName 批次名称
     * @param minScore 最低分数（可选）
     * @param maxScore 最高分数（可选）
     * @return 符合条件的专业组总数
     */
    default Long countMajorGroupsByScoreRange(
            String province, Integer year, String type, String batchName,
            Integer minScore, Integer maxScore) {
        
        LambdaQueryWrapper<AiAdmissionCutoffScoreDO> wrapper = new LambdaQueryWrapperX<AiAdmissionCutoffScoreDO>()
                .eqIfPresent(AiAdmissionCutoffScoreDO::getProvince, province)
                .eqIfPresent(AiAdmissionCutoffScoreDO::getYear, year)
                .eqIfPresent(AiAdmissionCutoffScoreDO::getType, type)
                .eqIfPresent(AiAdmissionCutoffScoreDO::getBatchName, batchName)
                .isNotNull(AiAdmissionCutoffScoreDO::getLowerScore)
                .ne(AiAdmissionCutoffScoreDO::getLowerScore, "")
                .ne(AiAdmissionCutoffScoreDO::getLowerScore, "-");
        
        List<AiAdmissionCutoffScoreDO> allResults = selectList(wrapper);
        
        // 在内存中过滤分数范围并统计
        return allResults.stream()
                .filter(record -> {
                    try {
                        String lowerScoreStr = record.getLowerScore();
                        if (lowerScoreStr == null || lowerScoreStr.trim().isEmpty()) {
                            return false;
                        }
                        Integer lowerScore = Integer.parseInt(lowerScoreStr.trim());
                        
                        if (minScore != null && lowerScore < minScore) {
                            return false;
                        }
                        if (maxScore != null && lowerScore > maxScore) {
                            return false;
                        }
                        return true;
                    } catch (NumberFormatException e) {
                        return false;
                    }
                })
                .count();
    }

}